﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using SqlSugar;
using System.Data;
using System.IO;
using Wechat_PublicNumber.Common;
using Wechat_PublicNumber.Entity;
using Wechat_PublicNumber.Model;

namespace Wechat_PublicNumber.Repository
{
    public class WorkRepository : DataAccess
    {
        [Autowired]
        private ExcelHelper excelHelper;

        /// <summary>
        /// 获取工作记录
        /// </summary>
        /// <param name="openID"></param>
        /// <returns></returns>
        public async Task<List<Work>> GetWorkListByOpenID(string openID) =>
            await WXDb.Queryable<Work>().Where(s => s.OpenID == openID).OrderBy(s => s.WhichDay).ToListAsync();

        /// <summary>
        /// 获取年度工作记录
        /// </summary>
        /// <param name="openID"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public async Task<List<Work>> GetWorkListByOpenID(string openID, int year)
        {
            var yearBegin = "year-01-01";

            var whichYear = DateTime.TryParse(yearBegin.Replace("year", year.ToString()), out var yearTime) ? yearTime : DateTime.Now;

            return await WXDb.Queryable<Work>().Where(s => s.OpenID == openID && s.WhichDay >= whichYear).OrderBy(s => s.WhichDay).ToListAsync();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="openID"></param>
        /// <param name="year"></param>
        /// <param name="pageCount"></param>
        /// <returns></returns>
        /// <exception cref="WeChatReplyUserException"></exception>
        public async Task<string> GetWorkListExcel(string openID, int year, int pageCount = 30)
        {
            var dataList = await GetWorkListByOpenID(openID, year);

            if (dataList.Count <= 0)
                throw new WeChatReplyUserException("暂无数据");

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("日期"));
            table.Columns.Add(new DataColumn("类型"));
            table.Columns.Add(new DataColumn("时长"));
            table.Columns.Add(new DataColumn("总计剩余时长"));

            decimal totalHours = 0;

            Dictionary<WorkTypeEmnu, decimal> typeTotalHours = new Dictionary<WorkTypeEmnu, decimal>();

            foreach (var item in dataList)
            {
                DataRow row = table.NewRow();

                row[0] = item.WhichDay.ToString("MM-dd");
                row[1] = item.Type.GetDescription();
                row[2] = $"{(item.Type == WorkTypeEmnu.WorkOvertime || item.Type == WorkTypeEmnu.YearHoliday ? "+" : "-")}{item.Hours}h";
                totalHours = item.Type == WorkTypeEmnu.WorkOvertime || item.Type == WorkTypeEmnu.YearHoliday ? totalHours += item.Hours : totalHours -= item.Hours;

                if (!typeTotalHours.ContainsKey(item.Type))
                    typeTotalHours.Add(item.Type, 0);

                typeTotalHours[item.Type] += item.Hours;

                if (totalHours < 0)
                    row[3] = $"{Math.Ceiling(totalHours / 8)}天{totalHours % 8}h";
                else
                    row[3] = $"{Math.Floor(totalHours / 8)}天{totalHours % 8}h";

                table.Rows.Add(row);
            }

            var fileName = $"WorkExcel/WorkList{DateTime.Now:yyyyyMMddHHmmssfff}.xls";

            var filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"wwwroot", fileName);

            string parm = string.Empty;

            foreach (var type in typeTotalHours)
            {
                parm += type.Value != 0 ? $"{type.Key.GetDescription()}:{type.Value}h " : "";
            }

            parm += $"总计剩余{totalHours}h";

            WorkListExcel(table, filePath, pageCount, parm);

            return filePath;

            //return $"http://101.42.227.212/WeChat/File/{fileName}";
        }

        /// <summary>
        /// 获取剩余调休时长
        /// </summary>
        /// <param name="openID"></param>
        /// <returns></returns>
        public async Task<decimal> GetResidueWorkTime(string openID)
        {
            var typeTotalHours = await WXDb.Queryable<Work>()
                                    .Where(s => s.OpenID == openID)
                                    .GroupBy(s => s.Type)
                                    .Select(s => new
                                    {
                                        Type = s.Type,
                                        TotalHours = SqlFunc.AggregateSum(s.Hours)
                                    }).ToListAsync();

            var workOvertime = typeTotalHours.Where(s => s.Type == WorkTypeEmnu.WorkOvertime || s.Type == WorkTypeEmnu.YearHoliday).Sum(s => s.TotalHours);

            var supplementBreaksTime = typeTotalHours.FirstOrDefault(s => s.Type == WorkTypeEmnu.SupplementBreaks)?.TotalHours ?? 0;

            return workOvertime - supplementBreaksTime;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <param name="path"></param>
        /// <param name="pageCount"></param>
        /// <param name="parm"></param>
        private void WorkListExcel(DataTable data, string path, int pageCount, string parm)
        {
            //创建工作簿
            var workbook = new HSSFWorkbook();
            //生成文件基本信息
            excelHelper.Generatesummaryinformation(workbook);
            //创建工作表
            var sheet = workbook.CreateSheet("加班补休表");
            sheet.SetColumnWidth(0, 8 * 256);
            sheet.SetColumnWidth(1, 8 * 256);
            sheet.SetColumnWidth(2, 8 * 256);
            sheet.SetColumnWidth(3, 19 * 256);
            //表头
            ICellStyle headerStyle = workbook.CreateCellStyle();//创建样式对象
            IFont font = workbook.CreateFont(); //创建一个字体样式对象
            font.FontName = "等线"; //和excel里面的字体对应
            font.Color = new HSSFColor.LightBlue().Indexed;//颜色参考NPOI的颜色对照表(替换掉PINK())
            font.FontHeightInPoints = 14;//字体大小
            font.IsBold = true;//字体加粗
            headerStyle.SetFont(font); //将字体样式赋给样式对象
            headerStyle.Alignment = HorizontalAlignment.Right;
            headerStyle.VerticalAlignment = VerticalAlignment.Center;
            //加班
            ICellStyle jbStyle = workbook.CreateCellStyle();//创建样式对象
            IFont jbfont = workbook.CreateFont(); //创建一个字体样式对象
            jbfont.FontName = "等线"; //和excel里面的字体对应
            jbfont.Color = new HSSFColor.Red().Indexed;//颜色参考NPOI的颜色对照表(替换掉PINK())
            jbfont.FontHeightInPoints = 11;//字体大小
            jbStyle.SetFont(jbfont); //将字体样式赋给样式对象
            jbStyle.Alignment = HorizontalAlignment.Right;

            //调休
            ICellStyle txStyle = workbook.CreateCellStyle();//创建样式对象
            IFont txfont = workbook.CreateFont(); //创建一个字体样式对象
            txfont.FontName = "等线"; //和excel里面的字体对应
            txfont.Color = new HSSFColor.Green().Indexed;//颜色参考NPOI的颜色对照表(替换掉PINK())
            txfont.FontHeightInPoints = 11;//字体大小
            txStyle.SetFont(txfont); //将字体样式赋给样式对象
            txStyle.Alignment = HorizontalAlignment.Right;

            //合并后cell
            ICellStyle hbStyle = workbook.CreateCellStyle();//创建样式对象
            IFont hbfont = workbook.CreateFont(); //创建一个字体样式对象
            hbfont.FontName = "等线"; //和excel里面的字体对应
            hbfont.Color = new HSSFColor.Red().Indexed;//颜色参考NPOI的颜色对照表(替换掉PINK())
            hbfont.FontHeightInPoints = 10;//字体大小
            hbStyle.SetFont(hbfont); //将字体样式赋给样式对象
            hbStyle.Alignment = HorizontalAlignment.Right;
            hbStyle.VerticalAlignment = VerticalAlignment.Center;


            var addHeaderSuccess = false;
            var headerTotal = 0;

            //处理数据
            if (data != null && data.Rows.Count > 0)
            {
                for (int rowindex = 0; rowindex < data.Rows.Count; rowindex++)
                {
                    IRow row = sheet.CreateRow(rowindex + headerTotal);

                    //创建标题行
                    if (rowindex % pageCount == 0 && !addHeaderSuccess)
                    {
                        CreateHeaderRow(row, data.Columns, headerStyle);
                        rowindex--;
                        headerTotal++;
                        addHeaderSuccess = true;
                        continue;
                    }
                    addHeaderSuccess = false;

                    //创建数据行
                    row.Height = 18 * 20;
                    for (int colindex = 0; colindex < data.Columns.Count; colindex++)
                    {
                        var cell = row.CreateCell(colindex);
                        var cellvalue = data.Rows[rowindex][colindex];
                        switch (data.Columns[colindex].DataType.Name)
                        {
                            case "byte":
                            case "int16":
                            case "int32":
                            case "int64":
                            case "decimal":
                            case "single":
                            case "double":
                                if (cellvalue != null && !cellvalue.Equals(DBNull.Value))
                                {
                                    cell.SetCellValue(double.TryParse(cellvalue.ToString(), out var doubleval) ? doubleval : 0);
                                }
                                break;
                            case "datetime":
                                DateTime dtval = DateTime.MinValue;
                                if (cellvalue != null && !cellvalue.Equals(DBNull.Value))
                                {
                                    DateTime.TryParse(cellvalue.ToString(), out dtval);
                                    if (dtval != DateTime.MinValue)
                                    {
                                        cell.SetCellValue(dtval);
                                    }
                                }
                                break;
                            default:
                                if (cellvalue != null && !cellvalue.Equals(DBNull.Value))
                                {
                                    cell.SetCellValue(cellvalue.ToString());
                                }
                                break;
                        }

                        if (data.Rows[rowindex][1].ToString() == "加班" || data.Rows[rowindex][1].ToString() == "年假")
                            cell.CellStyle = jbStyle;
                        else
                            cell.CellStyle = txStyle;
                    }
                }

                for (int i = 0; i < 3; i++)
                {
                    sheet.CreateRow(data.Rows.Count + headerTotal + i);
                }
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(data.Rows.Count + headerTotal, data.Rows.Count + headerTotal + 2, 0, 3));

                IRow hbrow = sheet.GetRow(data.Rows.Count + headerTotal);

                ICell hbcell = hbrow.CreateCell(0);

                hbcell.CellStyle = hbStyle;

                hbcell.SetCellValue(parm);
            }

            using (FileStream fs = File.Create(path)) //打开一个xls文件，如果没有则自行创建，如果存在则在创建时不要打开该文件！
            {
                workbook.Write(fs);    //向打开的这个xls文件中写入mysheet表并保存。
            }
            workbook.Close();
        }

        private void CreateHeaderRow(IRow row, DataColumnCollection columnList, ICellStyle cellStyle)
        {
            if (columnList.Count > 0)
            {
                row.Height = 24 * 20;
                for (int i = 0; i < columnList.Count; i++)
                {
                    var cell = row.CreateCell(i);
                    cell.SetCellValue(columnList[i].ColumnName);
                    cell.CellStyle = cellStyle;
                }
            }
        }
    }
}
